﻿package com.dbm.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import com.dbm.model.ConnPoolFactory;
import com.dbm.model.OracleFunctionUtil;
import com.dbm.util.PackageInfo;
import com.dbm.util.PackageItem;
import com.dbm.util.SoInfo;

public class CtlSoInfoDao {
	Logger log = Logger
			.getLogger(CtlSoInfoDao.class);
	DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	private OwnerCodeDao ownerCodeDao = new OwnerCodeDao();
	/**
	 * 新增包裹内商品明细
	 * @param soInfo
	 * @param id
	 * @param packageid
	 * @return
	 * @throws Exception
	 */
	public synchronized boolean addPackAgeItem(SoInfo soInfo,String id,String packageid) throws Exception{
		boolean flag = false;
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String addPackItemSql = "insert into TPACKAGEITEM (" +
				"ID, PACKAGEID, ITEMCODE, ITEMID, QUANTITY, " +
				"OWNERCODE)"+
				"values (?, ?, ?, ?, ?, ?)";
		PreparedStatement ctlStat = ctlconn.prepareStatement(addPackItemSql);
		ctlStat.setString(1, id);
		ctlStat.setString(2, packageid);
		ctlStat.setString(3, soInfo.getITEMCODE());
		ctlStat.setString(4, soInfo.getITEMID());
		ctlStat.setBigDecimal(5, soInfo.getACTUALQTY());
		ctlStat.setString(6, soInfo.getOWNERCODE());
		if(ctlStat.executeUpdate()==1){
			flag = true;
		}
		ctlStat.close();
		ctlconn.close();
		return flag;
	}
	
	/**
	 * 新增包裹内商品明细---多包裹
	 * @param PackageItem
	 * @param id
	 * @param packageid
	 * @return
	 * @throws Exception
	 */
	public synchronized boolean addPackAgeItem(PackageItem packageItem,String id,String packageid) throws Exception{
		boolean flag = false;
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String addPackItemSql = "insert into TPACKAGEITEM (" +
				"ID, PACKAGEID, ITEMCODE, ITEMID, QUANTITY, " +
				"OWNERCODE)"+
				"values (?, ?, ?, ?, ?, ?)";
		PreparedStatement ctlStat = ctlconn.prepareStatement(addPackItemSql);
		ctlStat.setString(1, id);
		ctlStat.setString(2, packageid);
		ctlStat.setString(3, packageItem.getITEMCODE());
		ctlStat.setString(4, packageItem.getITEMID());
		ctlStat.setBigDecimal(5, packageItem.getQUANTITY());
		ctlStat.setString(6, packageItem.getOWNERCODE());
		if(ctlStat.executeUpdate()==1){
			flag = true;
		}
		ctlStat.close();
		ctlconn.close();
		return flag;
	}
	
	
	/**
	 * 将发货数据写入 packageinfo表---多 package
	 * @param PackageInfo
	 * @return
	 */
	public synchronized boolean addPackageInfo(PackageInfo packageInfo,String packageId,String deliveryOrderCodeId) throws Exception{
		boolean flag = false;
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String addPageSql = "insert into TPACKAGEINFO " +
				"(DELIVERYORDERCODEID, LOGISTICSCODE, LOGISTICSNAME, EXPRESSCODE, PACKAGECODE, " +
				"LENGTH, WIDTH, HEIGHT, WEIGHT, VOLUME, " +
				"INVOICENO, THEORETICALWEIGHT, PACKAGEID)"
				+"values (?, ?, ?, ?, ?," +
				" 0.00, 0.00, 0.00, ?, 0.000, " +
				"?, 0, ?)";
		PreparedStatement ctlStat = ctlconn.prepareStatement(addPageSql);
		ctlStat.setString(1, deliveryOrderCodeId);
		ctlStat.setString(2, packageInfo.getLOGISTICSCODE());
		ctlStat.setString(3, packageInfo.getLOGISTICSNAME());
		ctlStat.setString(4, packageInfo.getEXPRESSCODE());
		ctlStat.setString(5, packageId);
		ctlStat.setBigDecimal(6, packageInfo.getWEIGHT());
		ctlStat.setString(7, packageInfo.getINVOICENO());
		ctlStat.setString(8, packageId);
		if(ctlStat.executeUpdate()==1){
			flag = true;
			log.info("新增packageInfo数据成功，id为"+packageId);
		}
		ctlStat.close();
		ctlconn.close();
		return flag;
	}
	
	
	/**
	 * 将发货数据写入 packageinfo表
	 * @param soInfo
	 * @return
	 */
	public synchronized boolean addPackageInfo(SoInfo soInfo,String packageId,String deliveryOrderCodeId) throws Exception{
		boolean flag = false;
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String addPageSql = "insert into TPACKAGEINFO " +
				"(DELIVERYORDERCODEID, LOGISTICSCODE, LOGISTICSNAME, EXPRESSCODE, PACKAGECODE, " +
				"LENGTH, WIDTH, HEIGHT, WEIGHT, VOLUME, " +
				"INVOICENO, THEORETICALWEIGHT, PACKAGEID)"
				+"values (?, ?, ?, ?, ?," +
				" 0.00, 0.00, 0.00, 0.000, 0.000, " +
				"?, 0, ?)";
		PreparedStatement ctlStat = ctlconn.prepareStatement(addPageSql);
		ctlStat.setString(1, deliveryOrderCodeId);
		ctlStat.setString(2, soInfo.getLOGISTICSCODE());
		ctlStat.setString(3, soInfo.getLOGISTICSNAME());
		ctlStat.setString(4, soInfo.getEXPRESSCODE());
		ctlStat.setString(5, soInfo.getPACKAGECODE());
		ctlStat.setBigDecimal(6, soInfo.getTHEORETICALWEIGHT());
		ctlStat.setString(7, packageId);
		if(ctlStat.executeUpdate()==1){
			flag = true;
			log.info("新增packageInfo数据成功，id为"+packageId);
		}
		ctlStat.close();
		ctlconn.close();
		return flag;
	}
	
	
	/**
	 * 将wms获取到的 soInfo 转换为 ctl 的 soInfo
	 * 
	 * @param soInfos
	 * @return
	 */
	public synchronized SoInfo WmsSoInfoConverCtlSoInfo(SoInfo wmsSoInfo) throws Exception{
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		PreparedStatement ctlStat = null;
		String a = OracleFunctionUtil.getSysConfiguration(wmsSoInfo.getWAREHOUSECODE(), wmsSoInfo.getOWNERCODE(), "TOP_QM_SKU");
		if("CODE".equals(a.toUpperCase())){//根据wms配置的参数判断是取 itemid为 sku 还是取 itemcode 为sku
		String ctlSoHeaderSql = "SELECT * FROM ctl_soinfo T where DELIVERYORDERCODE = ? and t.itemcode=? and t.planqty=?";
		ctlStat = ctlconn.prepareStatement(ctlSoHeaderSql);
//		System.out.println(ctlSoHeaderSql);
//		System.out.println(wmsSoInfo.getDELIVERYORDERCODE()+"\t"+wmsSoInfo.getPLANQTY()+"\t"+wmsSoInfo.getITEMCODE());
		ctlStat.setString(1, wmsSoInfo.getDELIVERYORDERCODE());
		ctlStat.setString(2, wmsSoInfo.getITEMCODE());
		ctlStat.setBigDecimal(3, wmsSoInfo.getPLANQTY());
		}
		if("ID".equals(a.toUpperCase())){//根据wms配置的参数判断是取 itemid为 sku 还是取 itemcode 为sku
			String ctlSoHeaderSql = "SELECT * FROM ctl_soinfo T where DELIVERYORDERCODE = ? and t.itemid=? and t.planqty=?";
			ctlStat = ctlconn.prepareStatement(ctlSoHeaderSql);
			ctlStat.setString(1, wmsSoInfo.getDELIVERYORDERCODE());
			ctlStat.setString(2, wmsSoInfo.getITEMID());
			ctlStat.setBigDecimal(3, wmsSoInfo.getPLANQTY());
		}
		ResultSet rs = ctlStat.executeQuery();
		SoInfo soInfo = new SoInfo();
		if (rs.next()) {
			soInfo.setAREA(rs.getString("aREA"));
			soInfo.setCARDIDNO(rs.getString("cARDIDNO"));
			soInfo.setCARNO(rs.getString("cARNO"));
			soInfo.setCITY(rs.getString("cITY"));
			soInfo.setCOMPANY(rs.getString("cOMPANY"));
			soInfo.setCONFIRMTYPE(wmsSoInfo.getCONFIRMTYPE());
			soInfo.setCOUNTRYCODE(rs.getString("cOUNTRYCODE"));
			soInfo.setCREATETIME(rs.getString("cREATETIME"));
			soInfo.setDELIVERYORDERCODE(rs.getString("dELIVERYORDERCODE"));
			soInfo.setDELIVERYORDERID(rs.getString("dELIVERYORDERID"));
			soInfo.setDESTINATION(rs.getString("dESTINATION"));
			soInfo.setDETAILADDRESS(rs.getString("dETAILADDRESS"));
			soInfo.setEMAIL(rs.getString("eMAIL"));
			soInfo.setEXPRESSCODE(rs.getString("eXPRESSCODE"));
			soInfo.setITEMCODE(rs.getString("iTEMCODE"));
			soInfo.setITEMID(rs.getString("iTEMID"));
			soInfo.setLOGISTICSCODE(rs.getString("lOGISTICSCODE"));
			soInfo.setLOGISTICSNAME(rs.getString("lOGISTICSNAME"));
			soInfo.setMOBILE(rs.getString("mOBILE"));
			soInfo.setNAME(rs.getString("nAME"));
			soInfo.setORDERCONFIRMTIME(rs.getString("oRDERCONFIRMTIME"));
			soInfo.setORDERTYPE(rs.getString("oRDERTYPE"));
			soInfo.setOUTBIZCODE(rs.getString("oUTBIZCODE"));
			soInfo.setPICKERINFOID(rs.getString("pICKERINFOID"));
			soInfo.setPROVINCE(rs.getString("pROVINCE"));
			soInfo.setRECEIVERID(rs.getString("rECEIVERID"));
			soInfo.setREMARK(rs.getString("rEMARK"));
			soInfo.setSCHEDULEDATE(rs.getString("sCHEDULEDATE"));
			soInfo.setSENDERID(rs.getString("sENDERID"));
			soInfo.setSENDFLAG(rs.getString("sENDFLAG"));
			soInfo.setSTATUS(wmsSoInfo.getSTATUS());
			soInfo.setTEL(rs.getString("tEL"));
			soInfo.setTOTALORDERLINES(rs.getBigDecimal("tOTALORDERLINES"));
			soInfo.setTOWN(rs.getString("tOWN"));
			soInfo.setTRANSPORTMODE(rs.getString("tRANSPORTMODE"));
			soInfo.setUSERID(rs.getString("uSERID"));
			soInfo.setWAREHOUSECODE(rs.getString("wAREHOUSECODE"));
			//TODO 代码测试时注释掉了，正式用时需要取消注释
			soInfo.setZIPCODE(rs.getString("zIPCODE"));
			soInfo.setOWNERCODE(rs.getString("oWNERCODE"));
			soInfo.setPLANQTY(rs.getBigDecimal("pLANQTY"));
			soInfo.setPURCHASEPRICE(rs.getBigDecimal("pURCHASEPRICE"));
			soInfo.setRETAILPRICE(rs.getBigDecimal("rETAILPRICE"));
			soInfo.setPRODUCTDATE(rs.getString("pRODUCTDATE"));
			soInfo.setEXPIREDATE(rs.getString("eXPIREDATE"));
			soInfo.setPRODUCECODE(rs.getString("pRODUCECODE"));
			soInfo.setBATCHCODE(rs.getString("bATCHCODE"));
			soInfo.setKEY1(rs.getString("kEY1"));
			soInfo.setKEY2(rs.getString("kEY2"));
			soInfo.setITEMID(rs.getString("itemid"));
			soInfo.setITEMNAME(rs.getString("iTEMNAME"));
			soInfo.setPLANQTY(rs.getBigDecimal("pLANQTY"));
			soInfo.setINVENTORYTYPE(rs.getString("iNVENTORYTYPE"));
			soInfo.setORDERLINENO(rs.getString("oRDERLINENO"));
			if(wmsSoInfo.getPLANQTY().equals(rs.getBigDecimal("PLANQTY"))&&wmsSoInfo.getITEMCODE().equals(rs.getString("iTEMCODE"))){//判断当前行是否为该SKU
				soInfo.setACTUALQTY(wmsSoInfo.getACTUALQTY());//如果是，则收货数量为当前SKU 的收货数量
			}else{
				soInfo.setACTUALQTY(new BigDecimal(0));//如果不是，则收货数量为 0
			}
		}
		rs.close();
		ctlStat.close();
		ctlconn.close();
		return soInfo;
	}

	/**
	 * 将wms获取到的 soInfo 转换为 ctl 的 soInfo
	 * 
	 * @param soInfos
	 * @return
	 */
	public synchronized List<SoInfo> WmsSoInfoToCtlSoInfo(SoInfo wmsSoInfo) throws Exception{
		List<SoInfo> ctlSoInfos = new ArrayList<SoInfo>();
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String ctlSoHeaderSql = "SELECT * FROM ctl_soinfo T where DELIVERYORDERCODE = ?";
		PreparedStatement ctlStat = ctlconn.prepareStatement(ctlSoHeaderSql);
//		System.out.println(ctlSoHeaderSql);
//		System.out.println(wmsSoInfo.getDELIVERYORDERCODE()+"\t"+wmsSoInfo.getPLANQTY()+"\t"+wmsSoInfo.getITEMCODE());
		ctlStat.setString(1, wmsSoInfo.getDELIVERYORDERCODE());
		
		ResultSet rs = ctlStat.executeQuery();
		if (rs.next()) {
			SoInfo soInfo = new SoInfo();
			soInfo.setAREA(rs.getString("aREA"));
			soInfo.setCARDIDNO(rs.getString("cARDIDNO"));
			soInfo.setCARNO(rs.getString("cARNO"));
			soInfo.setCITY(rs.getString("cITY"));
			soInfo.setCOMPANY(rs.getString("cOMPANY"));
			soInfo.setCONFIRMTYPE(rs.getBigDecimal("cONFIRMTYPE"));
			soInfo.setCOUNTRYCODE(rs.getString("cOUNTRYCODE"));
			soInfo.setCREATETIME(rs.getString("cREATETIME"));
			soInfo.setDELIVERYORDERCODE(rs.getString("dELIVERYORDERCODE"));
			soInfo.setDELIVERYORDERID(rs.getString("dELIVERYORDERID"));
			soInfo.setDESTINATION(rs.getString("dESTINATION"));
			soInfo.setDETAILADDRESS(rs.getString("dETAILADDRESS"));
			soInfo.setEMAIL(rs.getString("eMAIL"));
			soInfo.setEXPRESSCODE(rs.getString("eXPRESSCODE"));
			soInfo.setITEMCODE(rs.getString("iTEMCODE"));
			soInfo.setITEMID(rs.getString("iTEMID"));
			soInfo.setLOGISTICSCODE(rs.getString("lOGISTICSCODE"));
			soInfo.setLOGISTICSNAME(rs.getString("lOGISTICSNAME"));
			soInfo.setMOBILE(rs.getString("mOBILE"));
			soInfo.setNAME(rs.getString("nAME"));
			soInfo.setORDERCONFIRMTIME(rs.getString("oRDERCONFIRMTIME"));
			soInfo.setORDERTYPE(rs.getString("oRDERTYPE"));
			soInfo.setOUTBIZCODE(rs.getString("oUTBIZCODE"));
			soInfo.setPICKERINFOID(rs.getString("pICKERINFOID"));
			soInfo.setPROVINCE(rs.getString("pROVINCE"));
			soInfo.setRECEIVERID(rs.getString("rECEIVERID"));
			soInfo.setREMARK(rs.getString("rEMARK"));
			soInfo.setSCHEDULEDATE(rs.getString("sCHEDULEDATE"));
			soInfo.setSENDERID(rs.getString("sENDERID"));
			soInfo.setSENDFLAG(rs.getString("sENDFLAG"));
			soInfo.setSTATUS(wmsSoInfo.getSTATUS());
			soInfo.setTEL(rs.getString("tEL"));
			soInfo.setTOTALORDERLINES(rs.getBigDecimal("tOTALORDERLINES"));
			soInfo.setTOWN(rs.getString("tOWN"));
			soInfo.setTRANSPORTMODE(rs.getString("tRANSPORTMODE"));
			soInfo.setUSERID(rs.getString("uSERID"));
			soInfo.setWAREHOUSECODE(rs.getString("wAREHOUSECODE"));
			//TODO 代码测试时注释掉了，正式用时需要取消注释
			soInfo.setZIPCODE(rs.getString("zIPCODE"));
			soInfo.setOWNERCODE(rs.getString("oWNERCODE"));
			soInfo.setPLANQTY(rs.getBigDecimal("pLANQTY"));
			soInfo.setPURCHASEPRICE(rs.getBigDecimal("pURCHASEPRICE"));
			soInfo.setRETAILPRICE(rs.getBigDecimal("rETAILPRICE"));
			soInfo.setPRODUCTDATE(rs.getString("pRODUCTDATE"));
			soInfo.setEXPIREDATE(rs.getString("eXPIREDATE"));
			soInfo.setPRODUCECODE(rs.getString("pRODUCECODE"));
			soInfo.setBATCHCODE(rs.getString("bATCHCODE"));
			soInfo.setKEY1(rs.getString("kEY1"));
			soInfo.setKEY2(rs.getString("kEY2"));
			soInfo.setITEMID(rs.getString("itemid"));
			soInfo.setITEMNAME(rs.getString("iTEMNAME"));
			soInfo.setPLANQTY(rs.getBigDecimal("pLANQTY"));
			soInfo.setINVENTORYTYPE(rs.getString("iNVENTORYTYPE"));
			soInfo.setORDERLINENO(rs.getString("oRDERLINENO"));
			if(wmsSoInfo.getPLANQTY().equals(rs.getBigDecimal("PLANQTY"))&&wmsSoInfo.getITEMCODE().equals(rs.getString("iTEMCODE"))){//判断当前行是否为该SKU
				soInfo.setACTUALQTY(wmsSoInfo.getACTUALQTY());//如果是，则收货数量为当前SKU 的收货数量
			}else{
				soInfo.setACTUALQTY(new BigDecimal(0));//如果不是，则收货数量为 0
			}
			ctlSoInfos.add(soInfo);
		}
		rs.close();
		ctlStat.close();
		ctlconn.close();
		return ctlSoInfos;
	}

	/**
	 * 获取所有不重复的 DELIVERYORDERID
	 * 
	 * @return
	 */
	public synchronized List<String> getDeliveryOrderIdFromCtl()
			throws Exception {
		List<String> DeliveryOrderIds = new ArrayList<String>();
		String ctlSql = "select distinct DELIVERYORDERID from soinfo";
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		PreparedStatement ctlStat = ctlconn.prepareStatement(ctlSql);
		ResultSet rs = ctlStat.executeQuery();
		while (rs.next()) {
			String entryOrderId = rs.getString("deliveryOrderId");
			DeliveryOrderIds.add(entryOrderId);
		}
		rs.close();
		ctlStat.close();
		ctlconn.close();
		return DeliveryOrderIds;
	}

	/**
	 * 根据deliveryOrderId 获取相关订单信息
	 * 
	 * @param deliveryOrderId
	 * @return
	 */
	public synchronized List<SoInfo> getAllSoInfoFromCtl(String deliveryOrderId) throws Exception{
		List<SoInfo> soInfos = new ArrayList<SoInfo>();
		String cltSql = "SELECT * FROM soinfo WHERE DELIVERYORDERID=?";
			Connection ctlconn = ConnPoolFactory.getCtlConnection();
			PreparedStatement ctlStat = ctlconn.prepareStatement(cltSql);
			ctlStat.setString(1, deliveryOrderId);
			ResultSet rs = ctlStat.executeQuery();
			while (rs.next()) {
				SoInfo soInfo = new SoInfo();
				soInfo.setAREA(rs.getString("aREA"));
				soInfo.setCARDIDNO(rs.getString("cARDIDNO"));
				soInfo.setCARNO(rs.getString("cARNO"));
				soInfo.setCITY(rs.getString("cITY"));
				soInfo.setCOMPANY(rs.getString("cOMPANY"));
				soInfo.setCONFIRMTYPE(rs.getBigDecimal("cONFIRMTYPE"));
				soInfo.setCOUNTRYCODE(rs.getString("cOUNTRYCODE"));
				soInfo.setCREATETIME(rs.getString("cREATETIME"));
				soInfo.setDELIVERYORDERCODE(rs.getString("dELIVERYORDERCODE"));
				soInfo.setDELIVERYORDERID(rs.getString("dELIVERYORDERID"));
				soInfo.setDESTINATION(rs.getString("dESTINATION"));
				soInfo.setDETAILADDRESS(rs.getString("dETAILADDRESS"));
				soInfo.setEMAIL(rs.getString("eMAIL"));
				soInfo.setEXPRESSCODE(rs.getString("eXPRESSCODE"));
				soInfo.setITEMCODE(rs.getString("iTEMCODE"));
				soInfo.setITEMID(rs.getString("iTEMID"));
				soInfo.setLOGISTICSCODE(rs.getString("lOGISTICSCODE"));
				soInfo.setLOGISTICSNAME(rs.getString("lOGISTICSNAME"));
				soInfo.setMOBILE(rs.getString("mOBILE"));
				soInfo.setNAME(rs.getString("nAME"));
				soInfo.setORDERCONFIRMTIME(rs.getString("oRDERCONFIRMTIME"));
				soInfo.setORDERTYPE(rs.getString("oRDERTYPE"));
				soInfo.setOUTBIZCODE(rs.getString("oUTBIZCODE"));
				soInfo.setPICKERINFOID(rs.getString("pICKERINFOID"));
				soInfo.setPROVINCE(rs.getString("pROVINCE"));
				soInfo.setRECEIVERID(rs.getString("rECEIVERID"));
				soInfo.setREMARK(rs.getString("rEMARK"));
				soInfo.setSCHEDULEDATE(rs.getString("sCHEDULEDATE"));
				soInfo.setSENDERID(rs.getString("sENDERID"));
				soInfo.setSENDFLAG(rs.getString("sENDFLAG"));
				soInfo.setSTATUS(rs.getString("sTATUS"));
				soInfo.setTEL(rs.getString("tEL"));
				soInfo.setTOTALORDERLINES(rs.getBigDecimal("tOTALORDERLINES"));
				soInfo.setTOWN(rs.getString("tOWN"));
				soInfo.setTRANSPORTMODE(rs.getString("tRANSPORTMODE"));
				soInfo.setUSERID(rs.getString("uSERID"));
				soInfo.setWAREHOUSECODE(rs.getString("wAREHOUSECODE"));
				//TODO 代码测试时注释掉了，正式用时需要取消注释
				soInfo.setZIPCODE(rs.getString("zIPCODE"));
				soInfo.setOWNERCODE(ownerCodeDao.getCustomerIdByOwnerCode(rs.getString("oWNERCODE")));
				soInfo.setPLANQTY(rs.getBigDecimal("pLANQTY"));
				soInfo.setPURCHASEPRICE(rs.getBigDecimal("pURCHASEPRICE"));
				soInfo.setRETAILPRICE(rs.getBigDecimal("rETAILPRICE"));
				soInfo.setPRODUCTDATE(rs.getString("pRODUCTDATE"));
				soInfo.setEXPIREDATE(rs.getString("eXPIREDATE"));
				soInfo.setPRODUCECODE(rs.getString("pRODUCECODE"));
				soInfo.setBATCHCODE(rs.getString("bATCHCODE"));
				soInfo.setKEY1(rs.getString("kEY1"));
				soInfo.setKEY2(rs.getString("kEY2"));
				soInfo.setITEMID(rs.getString("itemid"));
				soInfo.setITEMNAME(rs.getString("iTEMNAME"));
				soInfo.setPLANQTY(rs.getBigDecimal("pLANQTY"));
				soInfo.setINVENTORYTYPE(rs.getString("iNVENTORYTYPE"));
				soInfo.setORDERLINENO(rs.getString("oRDERLINENO"));
				soInfos.add(soInfo);
				System.out.println(soInfo);
			}
			rs.close();
			ctlStat.close();
			ctlconn.close();
		return soInfos;
	}

	/**
	 * 修改已经进入wms数据的标记，方式下次再次被扫描到。
	 * 
	 * @param DELIVERYORDERID
	 * @return
	 * @throws Exception
	 */
	public synchronized boolean updateCtlSoHeaderInfo(String DELIVERYORDERID)
			throws Exception {
		boolean flag = false;
		// CTL数据库处理
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String ctlSql = "update tDeliveryOrderHandleInfo set sendflag='SEND' WHERE deliveryOrderId =?";
		PreparedStatement ctlStat = ctlconn.prepareStatement(ctlSql);
		// ctlStat.setString(1, soInfo.getDELIVERYORDERID()+"");
		ctlStat.setString(1, DELIVERYORDERID);
		if (ctlStat.executeUpdate() == 1) {
			flag = true;
			log.info("重置tDeliveryOrderHandleInfo 表id" + DELIVERYORDERID
					+ "的 sendflag 为 SEND 成功！");
		}
		ctlStat.close();
		ctlconn.close();
		return flag;
	}

	/**
	 * 进行订单确认,写入表头信息
	 * 
	 * @param soInfo
	 * @return
	 * @throws Exception
	 */
	public synchronized boolean addCtlSoHeader(String deliveryOrderId,
			SoInfo soInfo) throws Exception {
		boolean flag = false;
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		String ctlSql = "insert into TDELIVERYORDERHANDLEINFO "
				+ "(DELIVERYORDERID, TOTALORDERLINES, DELIVERYORDERCODE, ORDERTYPE, WAREHOUSECODE,"
				+ " CREATETIME, SCHEDULEDATE, LOGISTICSCODE, LOGISTICSNAME, TRANSPORTMODE,"
				+ " OUTBIZCODE, CONFIRMTYPE, STATUS, SENDFLAG, DESTINATION,"
				+ " EXPRESSCODE, ORDERCONFIRMTIME, PICKERINFOID, SENDERID, RECEIVERID)"
				+ "values (?, ?, ?, ?, ?," + " ?, ?, ?, ?, ?, "
				+ "?, ?, ?, ?, ?, " + "?, ?, ?, ?, ?)";
		PreparedStatement ctlHeaderStat = ctlconn.prepareStatement(ctlSql);
		ctlHeaderStat.setString(1, deliveryOrderId);
		ctlHeaderStat.setBigDecimal(2, soInfo.getTOTALORDERLINES());
		ctlHeaderStat.setString(3, soInfo.getDELIVERYORDERCODE());
		ctlHeaderStat.setString(4, soInfo.getORDERTYPE());
		ctlHeaderStat.setString(5, soInfo.getWAREHOUSECODE());
		ctlHeaderStat.setString(6, format.format(new Date()));
		ctlHeaderStat.setString(7, soInfo.getSCHEDULEDATE());
		ctlHeaderStat.setString(8, soInfo.getLOGISTICSCODE());
		ctlHeaderStat.setString(9, soInfo.getLOGISTICSNAME());
		ctlHeaderStat.setString(10, soInfo.getTRANSPORTMODE());
		ctlHeaderStat.setString(11, soInfo.getOUTBIZCODE());
		ctlHeaderStat.setBigDecimal(12, soInfo.getCONFIRMTYPE());
		ctlHeaderStat.setString(13, soInfo.getSTATUS());
		ctlHeaderStat.setString(14, "NO");
		ctlHeaderStat.setString(15, "QM");
		ctlHeaderStat.setString(16, soInfo.getEXPRESSCODE());
		ctlHeaderStat.setString(17, soInfo.getORDERCONFIRMTIME());
		ctlHeaderStat.setString(18, soInfo.getPICKERINFOID());
		ctlHeaderStat.setString(19, soInfo.getSENDERID());
		ctlHeaderStat.setString(20, soInfo.getRECEIVERID());
		if (ctlHeaderStat.executeUpdate() == 1) {
			flag = true;
			log.info("发货单" + soInfo.getDELIVERYORDERCODE() + "确认，状态："
					+ soInfo.getSTATUS() + "，写入表头信息完成， 主键Id："+deliveryOrderId);
		}
		ctlHeaderStat.close();
		ctlconn.close();
		return flag;
	}

	/**
	 * 处理CTL数据库出库单明细
	 * 
	 * @param asnInfos
	 * @return
	 * @throws Exception
	 */
	public synchronized int addOrderLineInfo(String entryOrderId,
			List<SoInfo> soInfos) throws Exception {
		int i = 0;
		Connection ctlconn = ConnPoolFactory.getCtlConnection();
		for (SoInfo soDe : soInfos) {
			String asnDetailSql = "insert into tentryOrderLines "
					+ "(ORDERLINEID, OUTBIZCODE, ORDERLINENO, OWNERCODE, ITEMCODE, "
					+ "ITEMID, ITEMNAME, PLANQTY, ACTUALQTY, SKUPROPERTY, "
					+ "PURCHASEPRICE, RETAILPRICE, INVENTORYTYPE, PRODUCTDATE, EXPIREDATE, "
					+ "PRODUCECODE, BATCHCODE, KEY1, KEY2, REMARK, "
					+ "SOURCEORDERCODE, SUBSOURCEORDERCODE, ENTRYORDERID)"
					+ "values (seqEntryOrderLines.Nextval,?,?,?,?,"
					+ "?,?,?,?,?," + "?,?,?,?,?," + "?,?,?,?,?," + "?,?,?)";
			PreparedStatement ctlStat = ctlconn.prepareStatement(asnDetailSql);
			ctlStat.setString(1, "");// 外部业务编码,消息ID,用于去重，当单据需要分批次发送时使用
										// ---outBizCode
			ctlStat.setString(2, soDe.getORDERLINENO());// 入库单的行号 ------orderLineNo
			ctlStat.setString(3, ownerCodeDao.getOwnerCodeByCustomerId(soDe.getOWNERCODE()));
			ctlStat.setString(4, soDe.getITEMCODE());
			ctlStat.setString(5, soDe.getITEMID());
			ctlStat.setString(6, soDe.getITEMNAME());
			ctlStat.setBigDecimal(7, soDe.getPLANQTY());
			ctlStat.setBigDecimal(8, soDe.getACTUALQTY());
			ctlStat.setString(9, soDe.getSKUPROPERTY());
			ctlStat.setBigDecimal(10, soDe.getPURCHASEPRICE());
			ctlStat.setBigDecimal(11, soDe.getRETAILPRICE());
			ctlStat.setString(12, soDe.getINVENTORYTYPE());
			ctlStat.setString(13, soDe.getPRODUCTDATE());
			ctlStat.setString(14, soDe.getEXPIREDATE());
			ctlStat.setString(15, soDe.getPRODUCECODE());
			ctlStat.setString(16, soDe.getBATCHCODE());
			ctlStat.setString(17, soDe.getKEY1());
			ctlStat.setString(18, soDe.getKEY2());
			ctlStat.setString(19, soDe.getREMARK());
			ctlStat.setString(20, "");// 交易平台订单--------sourceOrderCode
			ctlStat.setString(21, "");// 交易平台子订单编码----subSourceOrderCode
			ctlStat.setString(22, entryOrderId);
			if (ctlStat.executeUpdate() == 1) {
				i++;
			}
			ctlStat.close();
		}
		log.info("处理出库单:" + soInfos.get(0).getDELIVERYORDERCODE() + "明细信息完成，主键Id:"+entryOrderId);
		ctlconn.close();
		return i;
	}

}
